DB Unit 2 Assignment

The best way to get information you need from someone is to ask them a question. Same with a database. You have information entered and stored in your database. You can get a useful look at that information if you have a way to ask questions of your database. That is what a database is about.

For example, when a customer shops in a store, whether in person or (more useful) on line, the store owner(s) would like the ability to quickly review that customer's purchasing habits so he could direct them to specific items. This entails some sort of search by classifying information. In person, using the database interface, filtering records is a quick way of asking questions by selecting for specific data in an open table.

There are two basic methods of filtering in order to limit the records looked at:
1 - Filter by form - where you enter select criteria from pull-down text menus and apply it to a form (we get to forms in the next lesson).
2 - Filter by selection - where you look at information in the spreadsheet view and click on it to set specifications for which data to look for.

The Filter by Selection is by far the easiest and most obvious way to filter because you see the criteria you are selecting for as you pick it. Filter by selection also makes it easy to filter by a series of selected criteria in a type of "drill down" selection.

Original starting point

We are going to filter for all "Video" in the Tags field.

Place the cursor in any of the Tags field and right click.

The popup menus are the product of right-clicking in the field you intend to select from.

Notice the first level choices select for the entire field.

The second level choices select for information in only a part of the field.

Clicking the "Contains" produces a text box in which to enter the search term

Fill in the text box with your search term, in this case "Video"

The "Contains" means it will search for the word anywhere in that field, regardless of case (upper, lower)

Here is our filtered selection. Note the small filter-funnel icon on the right side of the column heading

Now we are down only to tags with "Video" in them

Let's add a filter for "Music of Our Lives" Use the minimal search term needed.

Again place the cursor in a field with the selection you want (Description), do a right click, choose contains and enter "lives"

Our new set of records with the second filter applied.

Note the filter-funnel icons are now showing on the right sides of the column headings for both fields with a filter.

This means that the record set we are viewing is the combined result of all filters.

Now we have only video of "Music of Our Lives"

On the Home Tab you will find the "Toggle Filter" button. Click that to clear all filters.

To clear only one filter at a time right-click inside a field and say yes to the "Clear filters from field?" prompt.

Once you click the toggle button you will have the full data display back.

You are ready to go again.

Part 2 - From Filters to Queries (stored, repeatable questions) Graded Item

Some of the questions you answer by filtering the database you will probably ask numerous times. Generally you will want to keep a regular lookout for certain types of information. Instead of always going through the steps for a filter, you can create a stored set of questions so that you can repeat your filter. A stored (and therefore, repeatable) question is called a Query.

Click on the "Create" tab, then on the "Query Wizard" icon.

This gives us a field selection. We could just click on the double chevron button and get all the fields, or, similar to right clicking in desired fields when we filtered, we could simply pick the fields we want to show.

Note that we are picking two kinds of fields
1 - those fields we are using to filter for information
2 - those fields we want to show based on the filter(s)

Here we've picked the five fields we want to show. Two of them, Tags and Description will be used to filter for (select for) the stored, repeatable question we are creating in this Query.

Set this to show all records selected.

Hitting the "Next" button (in the previous step) leads to this name-setting dialog.

The suggested name was "Inventory Query" which is fine. But we wanted something to tell us what repeatable question this one is so we've added "vid/lives" to the name.

Going with "Open ... to view ..." gets the next view. Just the fields selected but all records displayed.

Next we will set the query conditions.

first view of our new query

Unfortunately, while we can apply filters again at this point, we are not able to store them using filters. So, we need to go to Design Mode.

like any video anywhere on the line

Note that the asterisk is a "wild card" which means "anything here"

Don't bother typing in the full syntax (i.e "Like" or any equal sign) at this point. If you type in just what you are looking for then move your cursor to the next line, Access will fill in the syntax for you.

Here we have a couple of concepts.

Like "*video*"

means we are looking for a match somewhere in the field. That word "Like" and the quote marks were added.

We just type in *video* and Access filled in the Like and the quote marks.

The asterisk is used to indicate "anything here"

An asterisk on each end of our "search term" means to look for "video" anywhere in the field.

An asterisk at the end (video*) would mean to look for "video" at the start of the field (with anything after).

An asterisk at the start (*video) would mean at the end of the field (with anything before)

CIT105P Rubrics
Assignment 6-7-8: Database 1, 2, 3
Tables	10
Search Query	10
Report	10
Total	30
(Any other suggestions?)

CS 105 Database

Assignment 02 - Filters (asking questions)

Part 1 - Filters (asking questions in the moment)

Part 2 - From Filters to Queries (stored, repeatable questions) Graded Item